LIKE
This is one of my favorite logical operators because of its versatility and power. Hopefully by the end of this, LIKE will be one of your favorites too!
This operator is used primarily with wildcards. Before we get too deep into it, there are two primary wildcards used with the LIKE operator: the underscore (_) and the percent sign (%). The underscore (_) allows for the substitution of a single character in a specific position, while the percent sign (%) allows for the substitution of any number of characters, including none. Underscore is used less in my experience so our focus will be using LIKE with percent. Percent (%) can be used as either a beginning, ending, or wrapping wildcard. Let's look at a few examples.
SQL Syntax: Using LIKE
Example:
Employee Table
First_Name | Last_Name | Title
-----------|-----------|------------
Alice | Johnson | Asst. Mgr
Bob | Smith | Clerk
Raymond | Barone | Writer
Michael | Scott | Mgr
John | Doe | Chef
Bill | Jacobson | Clerk
Joseph | Robertson | Engineer
In the following examples pay attention to the placement of '%'. In SQL, string values must be enclosed in quotes when searching.
Begins With
Prompt
Find all information about employees whose titles begin with the letter 'C'.
Query
SELECT *
FROM Employee
WHERE Title LIKE 'C%'
Result
First_Name | Last_Name | Title
-----------|-----------|------------
Bob | Smith | Clerk
John | Doe | Chef
Bill | Jacobson | Clerk
This returned Bob, John, and Bill.
Contains
Prompt
Find the first and last name of all employees whose first name contains 'a'.
Query
SELECT First_Name, Last_Name
FROM Employee
WHERE First_Name LIKE '%a%'
Result
First_Name | Last_Name
-----------|-----------
Alice | Johnson
Raymond | Barone
Michael | Scott
This returned Alice, Raymond, and Michael. (Notice the placement of the %'s, we needed to wrap 'a' so that if it appeared in any row of the First_Name column, it was returned.)
Ends With
Prompt
Find the all information about all employees whose title end with 'r'.
Query
SELECT *
FROM Employee
WHERE Title LIKE '%r'
Result
First_Name | Last_Name | Title
-----------|-----------|------------
Alice | Johnson | Asst. Mgr
Raymond | Barone | Writer
Michael | Scott | Mgr
Joseph | Robertson | Engineer
This returned Alice, Raymond, Michael, and Joseph
LIKE Using _
Prompt
Find all information about employees whose second letter of their firstname is 'o'.
Query
SELECT *
FROM Employee
WHERE First_Name LIKE '_o%'
Result
First_Name | Last_Name | Title
-----------|-----------|------------
Bob | Smith | Clerk
John | Doe | Chef
This returned Bob and John.
Wrapping Up
The logical operator LIKE is powerful as it allows you to searxh portions of your data without needing to parse it. In a sense, the LIKE operator processes the data during a search but returns a complete set of results that match the specified pattern.